﻿Imports System.Data.SqlClient
Public Class dbUser
    Dim oEncript As New Crypt
    Dim Var As New varDeclare
    Public Function isValidLogin(ByVal UserID As String, ByVal Password As String) As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        MDI.LoginID = UserID
        'Password = oEncript.EncryptMD5(Password)
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        'command.CommandText = "SELECT SYRC_USER_NAME FROM SIS_ACCESS WHERE SYRC_USER_ID = @userID AND SYRC_USER_PAS = @password AND SYRC_USER_COMP_CODE = @Comp"
        command.CommandText = "SELECT usrFullName FROM tblUser WHERE usrLoginID = @userID AND usrLoginPassword = @password AND CompID = @Comp"
        command.Parameters.Add("@userID", SqlDbType.VarChar).Value = UserID
        command.Parameters.Add("@password", SqlDbType.VarChar).Value = Password
        command.Parameters.Add("@Comp", SqlDbType.VarChar).Value = Var.CompID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToString(result)
    End Function

    Public Function UserList(ByVal Comp As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)

        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        'command.CommandText = "SELECT SYRC_USER_NAME 'User Name' , SYRC_USER_ID 'User ID', SYRC_USER_PAS 'User Password' FROM SIS_ACCESS WHERE SYRC_USER_COMP_CODE = @Comp AND SYRC_USER_APPL = 'A'"
        command.CommandText = "SELECT usrFullName 'User Name', usrLoginID 'User ID', usrLoginPassword 'User Password' FROM tblUser WHERE usrStatus = 'A' AND CompID = @Comp"
        command.Parameters.Add("@Comp", SqlDbType.VarChar).Value = Comp
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function UserEntry(ByVal TypeAct As String, ByVal UserID As String, ByVal UserName As String, ByVal UserPass As String, ByVal EntryBy As String, ByVal Comp As String) As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_UserManagement"
        command.Parameters.Add("@Type", SqlDbType.VarChar).Value = TypeAct
        command.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID
        command.Parameters.Add("@UserName", SqlDbType.VarChar).Value = UserName
        If (UserPass <> "") Then
            'UserPass = oEncript.EncryptMD5(UserPass)
            command.Parameters.Add("@UserPass", SqlDbType.VarChar).Value = UserPass
        End If
        command.Parameters.Add("@EntryBy", SqlDbType.VarChar).Value = EntryBy
        command.Parameters.Add("@Comp", SqlDbType.VarChar).Value = Var.CompID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToString(result)
    End Function

    Public Function ChangePassword(ByVal UserID As String, ByVal NewPassword As String) As String
        Dim connection As SqlConnection
        Dim command As New SqlCommand
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_UserPasswordChange"
        command.Parameters.Add("@UserID", SqlDbType.VarChar).Value = UserID
        command.Parameters.Add("@UserPass", SqlDbType.VarChar).Value = NewPassword
        command.Parameters.Add("@Comp", SqlDbType.VarChar).Value = Var.CompID
        Dim result As Object = command.ExecuteScalar()
        connection.Close()
        Return Convert.ToString(result)
    End Function

    Public Function GetModulePermissionList(UserID As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT Module FROM v_ModulePermission WHERE LoginID = @LoginID AND CompID = @Comp"
        command.Parameters.Add("@LoginID", SqlDbType.VarChar).Value = UserID
        command.Parameters.Add("@Comp", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetFormPermissionList(UserID As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.Text
        command.CommandText = "SELECT * FROM v_FormPermission WHERE LoginID = @LoginID AND CompID = @Comp"
        command.Parameters.Add("@LoginID", SqlDbType.VarChar).Value = UserID
        command.Parameters.Add("@Comp", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function

    Public Function GetUserLoginDetail(UserID As String) As DataSet
        Dim connection As SqlConnection
        Dim adapter As SqlDataAdapter
        Dim command As New SqlCommand
        Dim ds As New DataSet
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        command.Connection = connection
        command.CommandType = CommandType.StoredProcedure
        command.CommandText = "proc_GetUserLoginDetail"
        command.Parameters.Add("@usrLoginID", SqlDbType.VarChar).Value = UserID
        command.Parameters.Add("@CompID", SqlDbType.VarChar).Value = Var.CompID
        adapter = New SqlDataAdapter(command)
        adapter.Fill(ds)
        connection.Close()
        Return ds
    End Function


    Public Function SetUserPermission(UserID As String, dtPermission As DataTable) As Boolean
        Dim result As Boolean = True
        Dim connection As SqlConnection
        Dim sqlTrans As SqlTransaction
        connection = New SqlConnection(Var.SUISConn)
        connection.Open()
        sqlTrans = connection.BeginTransaction()
        Try
            For i = 0 To dtPermission.Rows.Count - 1
                If (result) Then
                    Dim command As New SqlCommand
                    command.Connection = connection
                    command.Transaction = sqlTrans
                    command.CommandType = CommandType.StoredProcedure
                    command.CommandText = "proc_SetUserPermission"
                    command.Parameters.Add("@usrGenID", SqlDbType.VarChar).Value = UserID
                    command.Parameters.Add("@frmGenID", SqlDbType.VarChar).Value = dtPermission.Rows(i)("frmGenID").ToString()
                    command.Parameters.Add("@perOpen", SqlDbType.VarChar).Value = dtPermission.Rows(i)("perOpen").ToString()
                    command.Parameters.Add("@perInsert", SqlDbType.VarChar).Value = dtPermission.Rows(i)("perInsert").ToString()
                    command.Parameters.Add("@perUpdate", SqlDbType.VarChar).Value = dtPermission.Rows(i)("perUpdate").ToString()
                    command.Parameters.Add("@perDelete", SqlDbType.VarChar).Value = dtPermission.Rows(i)("perDelete").ToString()
                    command.Parameters.Add("@perPrint", SqlDbType.VarChar).Value = dtPermission.Rows(i)("perPrint").ToString()
                    Dim x As Object = command.ExecuteScalar()
                    result = Convert.ToString(x)
                End If
            Next
            If (result) Then
                sqlTrans.Commit()
            Else : sqlTrans.Rollback()
            End If
        Catch ex As Exception
            sqlTrans.Rollback()
        End Try
        connection.Dispose()
        connection.Close()
        Return result
    End Function
End Class
